
package qa;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import util.DBConnection;

public class Question
{

    public Question()
    {
        DB = new DBConnection();
    }

    public void setQid(int q_id)
    {
        this.q_id = q_id;
    }

    public int getQid()
    {
        return q_id;
    }

    public void setTitle(String title)
    {
        this.title = title;
    }

    public String getTitle()
    {
        return title;
    }

    public void setContent(String content)
    {
        this.content = content;
    }

    public String getContent()
    {
        return content;
    }

    public void setCid(int c_id)
    {
        this.c_id = c_id;
    }

    public int getCid()
    {
        return c_id;
    }

    public void setSid(String s_id)
    {
        this.s_id = s_id;
    }

    public String getSid()
    {
        return s_id;
    }

    public void setQtime(Date qtime)
    {
        this.qtime = qtime;
    }

    public Date getQtime()
    {
        return qtime;
    }

    public void setBrowseCount(int n)
    {
        browsecount = n;
    }

    public int getBrowseCount()
    {
        return browsecount;
    }

    public void setType(String type)
    {
        this.type = type;
    }

    public String getType()
    {
        return type;
    }

    public void setState(String state)
    {
        this.state = state;
    }

    public String getState()
    {
        return state;
    }
    
    public Question getQuestion(int qid){
    	String sql="select * from question where q_id="+qid;
    	Question question=new Question();
    	ResultSet rs=DB.executeQuery(sql);
    	try{
    		if(rs.next()){
    			question.setCid(rs.getInt("q_id"));
    			question.setCid(rs.getInt("c_id"));
    			question.setTitle(rs.getString("title"));
    			question.setContent(rs.getString("content"));
    			question.setQtime(rs.getTimestamp("qtime"));
    			question.setSid(rs.getString("s_id"));
    			question.setState(rs.getString("state"));
    			question.setBrowseCount(rs.getInt("browsecount"));
    			question.setType(rs.getString("type"));
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		DB.close(rs);
    		DB.close();
    	}
    	return question;
    }
    
    public String getAuthor(){
    	String sql="select nick_name from student where s_id='"+s_id+"'";
    	String author="";
    	ResultSet rs=DB.executeQuery(sql);
    	try{
    		if(rs.next())
    			author=rs.getString("nick_name");
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		DB.close(rs);
    		DB.close();
    	}
    	return author;
    }
    
    public ArrayList<Question> getMyQuestions(String sid, int cid, String type)
    {
        ArrayList<Question> questions = new ArrayList<Question>();
        ResultSet rs;
        String sql = "select * from question where s_id='"+sid+"' and c_id="+cid+" and type='"+type+"' order by qtime desc";
        rs = DB.executeQuery(sql);
        try
        {
            while(rs.next()) 
            {
                Question q = new Question();
                q.setQid(rs.getInt("q_id"));
                q.setTitle(rs.getString("title"));
                q.setContent(rs.getString("content"));
                q.setCid(rs.getInt("c_id"));
                q.setSid(rs.getString("s_id"));
                q.setQtime(rs.getTimestamp("qtime"));
                q.setType(rs.getString("type"));
                q.setBrowseCount(rs.getInt("browsecount"));
                q.setState(rs.getString("state"));
                questions.add(q);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }finally{
        	DB.close(rs);
            DB.close();
        }
        return questions;
    }

    public ArrayList<Question> getOtherQuestions(String sid, int cid, String type)
    {
        ArrayList<Question> questions = new ArrayList<Question>();
        ResultSet rs;
        String sql = "select * from question where s_id <> '"+sid+"' and c_id="+cid+" and type='"+type+"' order by browsecount desc";
        rs = DB.executeQuery(sql);
        try
        {
            while(rs.next()) 
            {
                Question q = new Question();
                q.setQid(rs.getInt("q_id"));
                q.setTitle(rs.getString("title"));
                q.setContent(rs.getString("content"));
                q.setCid(rs.getShort("c_id"));
                q.setSid(rs.getString("s_id"));
                q.setQtime(rs.getTimestamp("qtime"));
                q.setType(rs.getString("type"));
                q.setBrowseCount(rs.getInt("browsecount"));
                q.setState(rs.getString("state"));
                questions.add(q);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }finally{
        	DB.close(rs);
            DB.close();
        }
        return questions;
    }

    public ArrayList<Question> getQuestions(int cid, String type)
    {
        ArrayList<Question> questions = new ArrayList<Question>();
        ResultSet rs;
        String sql = "select * from question where c_id="+cid+" and type='"+type+"' order by qtime desc";
        rs = DB.executeQuery(sql);
        try
        {
            while(rs.next()) 
            {
                Question q = new Question();
                q.setQid(rs.getInt("q_id"));
                q.setTitle(rs.getString("title"));
                q.setContent(rs.getString("content"));
                q.setCid(rs.getShort("c_id"));
                q.setSid(rs.getString("s_id"));
                q.setQtime(rs.getTimestamp("qtime"));
                q.setType(rs.getString("type"));
                q.setBrowseCount(rs.getInt("browsecount"));
                q.setState(rs.getString("state"));
                questions.add(q);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }finally{
        	DB.close(rs);
            DB.close();
        }
        return questions;
    }

    public int getRespCount()
    {
        int count;
        ResultSet rs;
        count = 0;
        String sql = "selet count(*) as respCount from answer where q_id="+q_id;
        rs = DB.executeQuery(sql);
        try
        {
            if(rs.next())
                count = rs.getInt("respCount");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }finally{
        	DB.close(rs);
            DB.close();
        }
        return count;
    }

    public String getLatestRespTime()
    {
    	 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = "";
        ResultSet rs1;
        String sql1 = "select rtime from answer where q_id="+q_id+" order by rtime desc limit 1";
        rs1 = DB.executeQuery(sql1);
        try
        {
            if(rs1.next())
            {
                Date d1 = rs1.getTimestamp("rtime");
                time=dateFormat.format(d1);
            } else
            {
                String sql2 = "select qtime from question where q_id="+q_id;
                ResultSet rs2 = DB.executeQuery(sql2);
                if(rs2.next()){
                    Date d2 = rs2.getTimestamp("qtime");
                    time=dateFormat.format(d2);
                }
                DB.close(rs2);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }finally{
        	DB.close(rs1);
            DB.close();
        }
        return time;
    }
    public String getLatestAuthor(){
        String author = "";
        ResultSet rs;
        String sql = "select respondent from answer where q_id="+q_id+" order by rtime desc limit 1";
        rs = DB.executeQuery(sql);
        try
        {
            if(rs.next())
            {
                author = rs.getString("respondent");
            } else
            {
            	author=this.getAuthor();
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }finally{
        	DB.close(rs);
            DB.close();
        }
        return author;
    }

    public void proposeQuestion()
    {
        SimpleDateFormat dateFormat;
        PreparedStatement pstmt;
        dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "insert into question(q_id,title,content,c_id,s_id,qtime,type) values(null,?,?,?,?,?,?)";
        pstmt = DB.prepare(sql);
        try
        {
            pstmt.setString(1, title);
            pstmt.setString(2, content);
            pstmt.setInt(3, c_id);
            pstmt.setString(4, s_id);
            pstmt.setString(5, dateFormat.format(qtime));
            pstmt.setString(6, type);
            pstmt.executeUpdate();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }finally{
        	DB.close(pstmt);
            DB.close();
        }
    }
    
    public void increaseBrowseCount(int qid){
    	int count=browsecount+1;
    	String sql="update question set browsecount="+count+" where q_id="+qid;
    	DB.executeUpdate(sql);
    }
    private DBConnection DB;
    private int q_id;
    private String title;
    private String content;
    private int c_id;
    private String s_id;
    private Date qtime;
    private int browsecount;
    private String type;
    private String state;
    public static void main(String[] args){
    	Question question=new Question();
    	Date now=new Date();
    	question.setCid(Integer.parseInt("1"));
    	question.setTitle("title");
    	question.setContent("content");
    	question.setSid("20081120123");
    	question.setQtime(now);
    	question.setType("teacherOnly");
    	//question.proposeQuestion();
    	question.setQid(2);
    	System.out.println(question.getLatestRespTime());
    }
}
